# -*- coding: utf-8 -*-

import datetime
from EasyReportCode.easy.base.DbHelper import *
import logging

class pair54_cell_day():
    def __init__(self,startdate,enddate):
        """
        构造方法用于创建对象时使用，每当创建一个类的实例对象时，Python 解释器都会自动调用它;
        __init__() 方法可以包含多个参数，但必须包含一个名为 self 的参数，且必须作为第一个参数。也就是说，类的构造方法最少也要有一个 self 参数

        :param startdata 开始日期，格式 2021-08-31 字符串类型
        :param enddata 开始日期，格式 2021-08-31 字符串类型
        """
        self.startdate=startdate
        self.enddate=enddate

    # def GetDateList(self, startdate,enddate):
    #     """
    #     获取datelist，需要查询的时间列表
    #     """
    #     datelist = []  # 创建空列表
    #     datestart = datetime.datetime.strptime(startdate, '%Y-%m-%d')  #include startdate  将字符串解析为给定相应格式的 datetime对象
    #     dateend = datetime.datetime.strptime(enddate, '%Y-%m-%d')  #include enddate
    #     while datestart <= dateend:
    #         datelist.append(datestart.strftime('%Y-%m-%d'))  # 列表中添加元素
    #         datestart += datetime.timedelta(days=1)
    #     return datelist

    def GetDateList(self, startdate, enddate):
        datelist = [i.strftime('%Y-%m-%d') for i in pd.date_range(startdate, enddate)]
        return datelist

    # sqlite
    create_sql_day_vs="""
    CREATE TABLE if not exists spot_epsfb_54pair_cell_day (
      tt varchar(40),
      eci_5g bigint(40),
      eci_4g bigint(40),
      ho_54_fail bigint(20),
      user_nub bigint(20),
      flag bigint(20)
     )
    """

    # hive
    # create_sql_day_vs="""
    # CREATE EXTERNAL TABLE if not exists spot_epsfb_54pair_cell_day (
    #   tt string,
    #   eci_5g bigint,
    #   eci_4g bigint,
    #   ho_54_fail bigint,
    #   user_nub bigint,
    #   flag bigint
    #  );
    # """

    delete_sql_day_vs="""
    delete from spot_epsfb_54pair_cell_day where tt='%s'
    """

    insert_sql_day_vs="""
    insert into spot_epsfb_54pair_cell_day 
    select '%s' as tt,
    n2_handoverout_eci as eci_5g,
    n2_handoverout_othereci as eci_4g, 
    count(1) as ho_54_fail,
    count(distinct(call_imsi1)) as user_nub, 
    sum(case when n2_handoverout_procedurestatus=1 then 1 else 0 end ) as flag
    from fact_epsfb_bigxdr_%s
    where  epsfb_type=1 and epsfb_mode=1
    group by n2_handoverout_eci,n2_handoverout_othereci
    having sum(case when n2_handoverout_procedurestatus=1 then 1 else 0 end )=0 and count(1)>100 and count(distinct(call_imsi1))>3
    """

    insert_sql_day_vs="""
    INSERT INTO spot_epsfb_54pair_cell_day
    SELECT '%s' AS tt,
           n2_handoverout_eci AS eci_5g,
           n2_handoverout_othereci AS eci_4g,
           COUNT(1) AS ho_54_fail,
           COUNT(DISTINCT call_imsi1) AS user_nub,
           SUM(CASE WHEN n2_handoverout_procedurestatus = 1 THEN 1 ELSE 0 END) AS flag
    FROM fact_epsfb_bigxdr_%s
    WHERE epsfb_type = 1 AND epsfb_mode = 1
    GROUP BY n2_handoverout_eci, n2_handoverout_othereci
    HAVING SUM(CASE WHEN n2_handoverout_procedurestatus = 1 THEN 1 ELSE 0 END) = 0
           AND COUNT(1) > 100
           AND COUNT(DISTINCT call_imsi1) > 3;       
    """

    def get_data_spot_epsfb_54pair_cell_day(self):
        """
        用try except语句块捕获并处理异常
        首先执行 try 中的代码块，如果执行过程中出现异常，系统会自动生成一个异常类型，并将该异常提交给 Python 解释器，此过程称为捕获异常。
        当 Python 解释器收到异常对象时，会寻找能处理该异常对象的 except 块，如果找到合适的 except 块，则把该异常对象交给该 except 块处理，这个过程被称为处理异常。如果 Python 解释器找不到处理异常的 except 块，则程序运行终止，Python 解释器也将退出。
        """
        try:
            modifyTable('SQLITE_VS', self.create_sql_day_vs)
            for d in self.GetDateList(self.startdate, self.enddate):
                modifyTable('SQLITE_VS', self.delete_sql_day_vs % d)
                modifyTable('SQLITE_VS', self.insert_sql_day_vs % (d, d.replace('-', '')))
                # print("day insert " + self.insert_sql_day_vs % (d, d.replace('-', '')))
                print("day insert ")
                print(str(d) + " success")
        except Exception as e:
            logging.error(e)


    create_sql_week_vs="""
    CREATE TABLE if not exists spot_epsfb_54pair_cell_week (
      ttt varchar(40) ,
      eci_5g bigint(40),
      eci_4g bigint(40),
      ho_fail bigint(20),
      user bigint(20),
      flagg bigint(20)
      )
    """
    # ) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace'

    delete_sql_week_vs="""
    delete from spot_epsfb_54pair_cell_week where ttt='%s'
    """

    insert_sql_week_vs="""
    insert into spot_epsfb_54pair_cell_week

    select '%s' as ttt,
    eci_5g,eci_4g,
    sum(ho_54_fail) as ho_fail, 
    sum(user_nub) as user,
    count(1) as flagg
    from spot_epsfb_54pair_cell_day
    where tt in %s
    group by eci_5g,eci_4g
    having count(1)>3
    """

    def get_spot_epsfb_54pair_cell_week(self):

        try:
            modifyTable('SQLITE_VS', self.create_sql_week_vs)
            modifyTable('SQLITE_VS', self.delete_sql_week_vs % self.enddate)
            datelists=self.GetDateList(self.startdate,self.enddate)
            s='({})'.format(','.join("'"+str(i)+"'" for i in datelists))
            insert=self.insert_sql_week_vs%(self.enddate,s)
            print("week insert"+insert)
            modifyTable('SQLITE_VS', insert)
        except Exception as e:  # 抛出异常
            logging.error(e)


    def __call__(self):
        """
        __call__ 触发执行
        让类的实例的行为表现的像函数一样，你可以调用它们，将一个函数当做一个参数传到另外一个函数中等等。这是一个非常强大的特性，其让Python编程更加舒适甜美。对象后面加括号，触发执行。
        1、__call__ 方法的执行是由对象后加括号触发的，即：对象() 或者 类()()
        2、__call__ 在那些类的实例经常改变状态的时候会非常有效。调用这个实例是一种改变这个对象状态的直接和优雅的做法。
        """
        try:
            self.get_data_spot_epsfb_54pair_cell_day()
            self.get_spot_epsfb_54pair_cell_week()

            sql="""
                select cast(replace(ecgi,'446000','')as int) as new_4g_ecgi, 
                ciname as ciname_4g
                from dim_ci
                """
            df_4g_ci=selectTable('SQLITE_VS', sql)

            sql="""
                select  cast(ecgi as int) as   new_5g_ecgi, 
                ciname as ciname_5g
                from dim_ci
                """
            df_5g_ci=selectTable('SQLITE_VS', sql)

            sql="""
                select * from spot_epsfb_54pair_cell_week where ttt='%s'
                order by ho_fail desc 
                """
            df_res=selectTable('SQLITE_VS', sql % self.enddate)  # 按切换失败此处排序

            df_res = pd.merge(df_res,df_5g_ci,left_on=['eci_5g'],right_on=['new_5g_ecgi'],how='left')  # 匹配5G工参
            df_res = pd.merge(df_res,df_4g_ci,left_on=['eci_4g'],right_on=['new_4g_ecgi'],how='left')  # 匹配4G工参

            return df_res
        except Exception as e:
            logging.error(e)


def getData(startdate,enddate):
    df_res= pair54_cell_day(startdate,enddate)()
    return df_res


# getData('2022-06-14','2022-06-20')